package com.ucom;

/**
 * 
 */


import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.LinkedList;

/**
 * @author Tathagata
 * @author Joseph Grasser
 * 
 */
public class UcomStudentWorkloadDifferenceDAO {

        /**
         * 
         */
        private Connection dbConnection;
        private String query;
        private ResultSet rslt;
        private Statement stmtGetListEntries;

        public UcomStudentWorkloadDifferenceDAO() {
                try {
                	Class.forName("org.apache.derby.jdbc.ClientDriver");
        			dbConnection = DriverManager.getConnection(
        					"jdbc:derby://localhost:1527/UCOM");
                        stmtGetListEntries = dbConnection.createStatement(
                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_READ_ONLY);

                } catch (ClassNotFoundException cnfe) {
                        System.out.println("Error in creating Database conncetion");
                        System.out.println(cnfe.getMessage());

                } catch (SQLException sqle) {
                        System.out.println("Error in creating Database conncetion");
                        System.out.println(sqle.getMessage());
                }

        }

        public LinkedList<Query7> query7() {
                LinkedList<Query7> result = new LinkedList<Query7>();
                try {
                                query = "SELECT X.USER_ID,Y.USER_ID, X.WORKLOADG - Y.WORKLOADL"
										+ "FROM"
										+ "(SELECT COUNT(*) AS WORKLOADG, USER_ID"
										+ "FROM USAGE_HISTORY"
										+ "WHERE USER_ID IN"
										+ "			  (SELECT USER_ID"
										+ "			  FROM (SELECT AVG(SCORE)/25.0 AS GPA, USER_ID"
										+ "					  FROM TRANSCRIPT"
										+ "					  GROUP BY USER_ID) T"
										+ "					  WHERE T.GPA>3.2)"
										+ "GROUP BY USER_ID)X,"
										+ "(SELECT COUNT(*) AS WORKLOADL, USER_ID"
										+ "FROM USAGE_HISTORY"
										+ "WHERE USER_ID IN"
										+ "			  (SELECT USER_ID"
										+ "			  FROM (SELECT AVG(SCORE)/25.0 AS GPA, USER_ID"
										+ "					  FROM TRANSCRIPT"
										+ "					  GROUP BY USER_ID) T"
										+ "					  WHERE T.GPA<3.2)"
										+ "GROUP BY USER_ID)Y";

                
                        rslt = stmtGetListEntries.executeQuery(query);
                        
                        while (rslt.next()) {
                                System.out.println("hello there");
                                System.out.print(rslt.getString(1));
                                System.out.print("\t" + rslt.getInt(2));
                                System.out.println("\t" + rslt.getInt(3));
                                result.add(new Query7(rslt.getString(1), rslt.getString(2), rslt.getInt(3)));
                        }
                        rslt.close();
                } catch (SQLException sqle) {
                        System.out.println("Error in getting transaction details");
                        System.out.println(sqle.getMessage());
                } catch (Exception e) {
                        e.printStackTrace();
                }
                return result;
        }
}
